package seedpod.rdb;

import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;

import seedpod.model.rdb.Attribute;
import seedpod.model.rdb.Rdb;
import seedpod.model.rdb.RdbValueType;
import seedpod.model.rdb.Relation;
import seedpod.model.rdb.SimpleForeignKey;
import seedpod.model.rdb.View;
import seedpod.model.rdb.ViewReference;

/**
 * Writes out create table SQL statements for DB definition. Generic insert statements Copyright 2009 University of
 * Washington This code is released under the GPL version 3 - with a copy of the license included
 * 
 * @author Maya Hao Li
 */

public class RdbSql {

	public static final String	AND				= " AND ";
	public static final String	CHECK			= " CHECK ";
	public static final String	CREATE_TABLE	= "CREATE TABLE ";
	public static final String	DEFAULT			= " DEFAULT ";
	public static final String	EQUALS			= " = ";
	public static final String	FROM			= " FROM ";
	public static final String	GREATER_THAN	= ">";
	public static final String	IN				= " IN ";
	public static final String	INSERT_INTO		= "INSERT INTO ";
	public static final String	LESS_THAN		= "<";
	public static final String	NOT_NULL		= " NOT NULL ";
	public static final String	NULL			= " NULL ";
	public static final String	PRIMARY_KEY		= " PRIMARY KEY ";
	public static final String	SELECT			= " SELECT ";
	public static final String	UNIQUE			= " UNIQUE ";
	public static final String	VALUES			= " VALUES ";
	public static final String	WHERE			= " WHERE ";
	public static final String	SET				= " SET ";
	public static final String	UPDATE			= "UPDATE ";

	public static StringBuffer createDB( Rdb rdb ) {
		StringBuffer cb = new StringBuffer();
		cb.append( "CREATE DATABASE" + SqlUtil.SP );
		cb.append( SqlUtil.QQ( rdb.getName() ) );
		cb.append( ';' );
		cb.append( SqlUtil.NL );
		return cb;
	}

	public static StringBuffer createForeignKey( SimpleForeignKey fk ) {
		StringBuffer str = new StringBuffer();

		str.append( "ALTER TABLE " );
		str.append( SqlUtil.QQ( fk.getContainerRelationName() ) );
		str.append( " ADD CONSTRAINT " );
		str.append( SqlUtil.QQ( "fk_" + fk.getName() ) );
		str.append( " FOREIGN KEY (" + SqlUtil.QQ( fk.getName() ) + ") " );
		str.append( " REFERENCES " + SqlUtil.QQ( fk.getReferencedAttribute().getContainerRelationName() ) );
		str.append( " ON DELETE CASCADE ;" );
		str.append( SqlUtil.NL + SqlUtil.NL );
		return str;
	}

	public static StringBuffer createTable( Relation rel ) {
		Vector<Attribute> attributes = rel.getAttributes();
		StringBuffer s_table = new StringBuffer().append( CREATE_TABLE + SqlUtil.SP + SqlUtil.QQ( rel.getName() ) + SqlUtil.NL );

		StringBuffer s_att = new StringBuffer();
		for ( int i = 0; i < attributes.size(); i++ ) {
			Attribute a = attributes.elementAt( i );
			if ( a.isImplemented() ) s_att.append( defineAttribute( a ) + SqlUtil.NL );
		}

		s_att.append( definePrimaryKeys( rel ) );
		s_table.append( SqlUtil.PARAN( s_att.toString() ) + SqlUtil.SC ).append( SqlUtil.NL + SqlUtil.NL );
		return s_table;
	}

	public static StringBuffer createView( View view ) {
		StringBuffer str = new StringBuffer();
		str.append( "CREATE VIEW " + SqlUtil.QQ( view.getName() ) + " AS " );

		Vector<Attribute> attributes = view.getAttributes();
		StringBuffer select = new StringBuffer().append( " SELECT " );
		for ( int i = 0; i < attributes.size(); i++ ) {
			Attribute a = attributes.elementAt( i );
			if ( !a.isImplemented() ) continue;

			select.append( SqlUtil.QQ( a.getName() ) + SqlUtil.C );
		}
		select.deleteCharAt( select.lastIndexOf( SqlUtil.C ) );

		// TODO should union the view but not the tables?
		Vector<Relation> unionTables = view.getUnionRelations();
		String viewName;
		for ( int i = 0; i < unionTables.size(); i++ ) {
			Relation table = unionTables.elementAt( i );
			if ( table == null ) {
				System.err.println( view.getName() + " union found null table" );
				continue;
			}

			viewName = table.getName();
			str.append( SqlUtil.NL + SqlUtil.TAB + select + " FROM " + SqlUtil.QQ( viewName ) );
			if ( i < unionTables.size() - 1 ) str.append( (SqlUtil.NL + " UNION ") );
		}
		str.append( SqlUtil.SC + SqlUtil.NL + SqlUtil.NL );
		return str;
	}

	/**
	 * View references are references to an attribute in a view, similar to foreign keys for tables. These are
	 * implemented as rules.
	 * 
	 * @param vk
	 * @return
	 */
	public static StringBuffer createViewReference( ViewReference vk ) {
		Attribute toAtt = vk.getReferencedAttribute();
		StringBuffer str = new StringBuffer();
		String fromTable = SqlUtil.QQ( vk.getContainerRelationName() );
		String toTable = SqlUtil.QQ( toAtt.getContainerRelationName() );
		String toAttName = toTable + "." + SqlUtil.QQ( toAtt.getName() );
		String vkName = fromTable + "." + SqlUtil.QQ( vk.getName() );
		String[] events = { "INSERT", "UPDATE" };

		StringBuffer conditionStr = new StringBuffer();

		conditionStr.append( "SELECT" + SqlUtil.SP + toAttName + SqlUtil.SP );
		conditionStr.append( "FROM" + SqlUtil.SP + toTable + SqlUtil.SP );
		conditionStr.append( "WHERE" + SqlUtil.SP + toAttName + "=" + vkName );

		for ( String event : events ) {
			String ruleName = "r." + event + "." + vk.getName();
			str.append( "CREATE RULE" + SqlUtil.SP + SqlUtil.QQ( ruleName ) + SqlUtil.SP + "AS ON" + SqlUtil.SP );
			str.append( event + SqlUtil.NL );
			str.append( "TO" + SqlUtil.SP + fromTable + SqlUtil.NL );
			str.append( "DO SELECT " + vkName + SqlUtil.SP );
			str.append( "FROM" + SqlUtil.SP + fromTable + SqlUtil.SP + "WHERE EXISTS" + SqlUtil.NL );
			str.append( SqlUtil.PARAN( conditionStr.toString() ) + SqlUtil.SC + SqlUtil.NL + SqlUtil.NL );
		}
		return str;
	}

	protected static String defineAttribute( Attribute a ) {
		String s = "";

		s += SqlUtil.QQ( a.getName() ) + SqlUtil.SP;
		s += a.getDatabaseTypeString() + SqlUtil.SP;
		// s += a.isPrimaryKey() ? PRIMARY_KEY + SqlUtil.SP : SqlUtil.SP;
		s += a.isUnique() ? UNIQUE + SqlUtil.SP : SqlUtil.SP;
		s += a.isRequired() ? NOT_NULL + SqlUtil.SP : SqlUtil.SP;
		s += defineDefault( a );
		s += defineConstraint( a );
		s += SqlUtil.C;
		s += a.getComment();
		return s;
	}

	protected static String defineConstraint( Attribute a ) {
		String s = "";

		if ( a.getRdbValueType().equals( RdbValueType.VARCHAR ) ) s = defineStringConstraint( a );
		else if ( a.getRdbValueType().equals( RdbValueType.INTEGER ) || a.getRdbValueType().equals( RdbValueType.NUMERIC ) ) s = defineNumericConstraint( a );
		return s;
	}

	protected static String defineDefault( Attribute a ) {
		String defaultStr = "";

		String defaultValue = a.getDefaultValue();

		if ( defaultValue != null && defaultValue.length() != 0 && !defaultValue.equalsIgnoreCase( DEFAULT ) ) {
			defaultStr = defaultValue;

			// single quote text default values
			if ( a.getRdbValueType().equals( RdbValueType.CHARACTER ) || a.getRdbValueType().equals( RdbValueType.VARCHAR ) || a.getRdbValueType().equals( RdbValueType.TEXT ) ) if ( !defaultValue.equalsIgnoreCase( "NULL" ) ) defaultStr = SqlUtil.Q( defaultValue );

			defaultStr = SqlUtil.SP + DEFAULT + SqlUtil.SP + defaultStr + SqlUtil.SP;
		}
		return defaultStr;
	}

	protected static String defineNumericConstraint( Attribute a ) {
		String s, maxStr, minStr;
		s = maxStr = minStr = "";
		Number max = a.getMaximumValue();
		Number min = a.getMinimumValue();
		boolean hasMax = false, hasMin = false;

		if ( max != null ) {
			maxStr = a.getName() + SqlUtil.SP + LESS_THAN + SqlUtil.SP + max.doubleValue();
			hasMax = true;
		}

		if ( min != null ) {
			minStr = a.getName() + SqlUtil.SP + GREATER_THAN + SqlUtil.SP + min.doubleValue();
			hasMin = true;
		}

		s += hasMax ? maxStr : "";
		s += hasMax && hasMin ? SqlUtil.SP + AND + SqlUtil.SP : "";
		s += hasMin ? minStr : "";

		if ( hasMax || hasMin ) s = CHECK + SqlUtil.SP + SqlUtil.PARAN( s );

		return s;
	}

	private static StringBuffer definePrimaryKeys( Relation rel ) {
		Vector<Attribute> keys = rel.getPrimaryKeySet();
		StringBuffer s = new StringBuffer().append( PRIMARY_KEY );
		StringBuffer s_key = new StringBuffer();

		if ( keys.size() == 0 ) return new StringBuffer();

		for ( int i = 0; i < keys.size(); i++ ) {
			s_key.append( SqlUtil.QQ( keys.elementAt( i ).getName() ) );
			if ( i < keys.size() - 1 ) s_key.append( SqlUtil.C );
		}

		s.append( SqlUtil.PARAN( s_key ) );
		return s;
	}

	private static String defineStringConstraint( Attribute a ) {
		String s = "";
		Collection<String> aVals = a.getAllowedValues();
		if ( aVals != null ) {
			String optionStr = "";
			int numOption = 0;
			for ( String string : aVals ) {
				numOption++;
				if ( numOption > 1 ) optionStr += SqlUtil.C;
				optionStr += SqlUtil.Q( string );
			}

			s += CHECK + SqlUtil.SP + SqlUtil.PARAN( SqlUtil.QQ( a.getName() ) + SqlUtil.SP + IN + SqlUtil.SP + SqlUtil.PARAN( optionStr ) );
		}
		return s;
	}

	public static String dropTable( Relation rel ) {
		return "DROP TABLE " + SqlUtil.QQ( rel.getName() ) + " CASCADE; " + SqlUtil.NL;
	}

	public static String dropView( View view ) {
		return "DROP VIEW " + SqlUtil.QQ( view.getName() ) + " CASCADE; " + SqlUtil.NL;
	}

	/**
	 * prepare statement for insert data TODO test this prepared statement version of the function *
	 */
	/*
	 * public static PreparedStatement getInsertPreparedStatement( Connection dbConn, Relation rel, HashMap<String,
	 * Object> data ) throws SQLException { PreparedStatement insertStmt; String insertStr =INSERT_INTO + SqlUtil.QQ(
	 * rel.getName() ) + SqlUtil.NL + SqlUtil.TAB; String keyString = "", valString = ""; Vector<Attribute> attributes =
	 * rel.getAttributes(); // data.keySet().iterator(); for ( int i = 0; i < attributes.size(); i++ ) { Attribute att =
	 * (Attribute)attributes.elementAt( i ); keyString += SqlUtil.QQ( att.getName() ); if (
	 * att.getRdbValueType().equals( RdbValueType.SERIAL ) ) valString += DEFAULT; else valString += "?"; keyString +=
	 * (i < attributes.size() - 1) ? SqlUtil.C : ""; valString += (i < attributes.size() - 1) ? SqlUtil.C : ""; }
	 * insertStr += SqlUtil.PARAN( keyString ) + SqlUtil.SP + VALUES + SqlUtil.SP + SqlUtil.PARAN( valString ) +
	 * SqlUtil.SC; insertStmt = dbConn.prepareStatement( insertStr ); for ( int i = 1; i < attributes.size(); i++ ) {
	 * Attribute att = (Attribute)attributes.elementAt( i ); Object val = data.get( att.getName() );
	 * setPreparedStmtValue( insertStmt, i, att.getRdbValueType(), val ); } return insertStmt; }
	 */

	/*
	 * public static String getUpdateRowSql( ClsMap relation, HashMap<SlotMap, AVPair> values, Integer oid ) throws
	 * Exception { String updateStmt = UPDATE + SqlUtil.QQ( relation.getRelationName() ) + SET; Collection<SlotMap>
	 * slotCollection = relation.getSlotMaps(); Iterator<SlotMap> slotItr = slotCollection.iterator(); boolean firstAtt
	 * = true; while (slotItr.hasNext()) { if ( !firstAtt ) updateStmt += ", "; else firstAtt = false; SlotMap att =
	 * slotItr.next(); AVPair binder = values.get( att ); updateStmt += att.getName() + EQUALS + toSqlValString(
	 * binder.getValue(), att ); } updateStmt += WHERE; return null; }
	 */

	public static StringBuffer select( Relation rel ) {
		return select( rel, null, null );
	}

	/*
	 * private static String toSqlValString( Object val, SlotMap att ) throws SeedpodException { RdbValueType type =
	 * att.getRdbValueType(); if ( val == null && att.getDefaultValue() != null ) return DEFAULT; else if ( val == null
	 * && !att.isNullable() ) { throw new SeedpodException( att.getName() + " in " + att.getDomainCls() +
	 * " value is required " ); } return toSqlValString( val, type ); }
	 */

	public static StringBuffer select( Relation rel, Vector<Attribute> columns, String filter ) {
		return selectSql( rel.getName(), columns, filter );
	}

	/**
	 * constructs a select query from table relName. columns can be null if selects all (*). Filter can be null.
	 * 
	 * @param relName
	 * @param columns
	 * @param filter
	 * @return
	 */
	public static StringBuffer selectSql( String relName, Vector<Attribute> columns, String filter ) {
		StringBuffer q = new StringBuffer( SELECT );

		if ( columns == null ) q.append( " * " );
		else for ( int i = 0; i < columns.size(); i++ ) {
			Attribute a = columns.elementAt( i );
			q.append( a.getName() );
			if ( i < columns.size() - 1 ) q.append( SqlUtil.C );
			q.append( SqlUtil.SP );
		}

		q.append( FROM );
		q.append( SqlUtil.QQ( relName ) );

		if ( filter != null && filter.length() > 0 ) {
			if ( !(filter.trim().startsWith( "ORDER BY" ) || filter.trim().startsWith( "LIMIT" )) ) q.append( WHERE );
			q.append( filter );
		}

		return q;
	}


	public static String toSqlValString( Object val, Attribute att ) throws Exception {
		RdbValueType type = att.getRdbValueType();
		if ( val == null && att.getDefaultValue() != null ) return DEFAULT;
		else if ( val == null && att.isRequired() ) throw new Exception( att.getName() + " in " + att.getRelation().getName() + " value is required " );
		return toSqlValString( val, type );
	}

	public static String toSqlValString( Object val, RdbValueType type ) {
		String stringVal = type.valueToString( val );
		return type.equals( RdbValueType.BOOLEAN )
			|| type.equals( RdbValueType.CHARACTER ) 
			|| type.equals( RdbValueType.TEXT ) 
			|| type.equals( RdbValueType.DATE ) 
			|| type.equals( RdbValueType.VARCHAR ) ? SqlUtil.dollarQuote( stringVal, null ) : stringVal;
	}
}
